<?php
if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Setup_model extends CI_Model {

    function __construct()
	{
		parent:: __construct();
	}
	
	//===============================================================================================================
    // WAREHOUSE MODEL ~ SURYA ALAM GULTOM 
    //===============================================================================================================
	
	function item_list($arr) {
        if (!empty($arr['warehouse_id'])) {
        
			$sql = "
				select house.WAREHOUSE_ID,
				   house.WAREHOUSE_ITEM_NAME,
				   house.WAREHOUSE_ITEM_QTY,
				   house.WAREHOUSE_ITEM_QTY_IN,
				   house.WAREHOUSE_ITEM_QTY_OUT,
				   house.WAREHOUSE_ITEM_STATUS,
				   house.WAREHOUSE_ITEM_ACTIVE,
				   house.WAREHOUSE_ITEM_NOT_ACTIVE
				from fm_warehouse house
				where house.WAREHOUSE_ID = '" . $arr['warehouse_id']."'  
			";					

			$query = $this->db->query($sql);
            return $query->row_array();
        }
    }
	
	function list_warehouse_item($arr, $limit) {
		$whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_name'])) {
                $whereCond .= " and upper(warehouse_item_name) like upper('%" . $arr['search_by_name'] . "%')";
            }
            if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(warehouse_item_status) = upper('" . $arr['search_by_status'] . "')";
            }
        }

		$sql = "
			select RECNUM, warehouse_id,
				   warehouse_item_name,
				   warehouse_item_qty,
				   warehouse_item_qty_in,
				   warehouse_item_qty_out,
				   WAREHOUSE_ITEM_ACTIVE,
				   WAREHOUSE_ITEM_NOT_ACTIVE,
				   warehouse_item_status
			from (
			select rownum RECNUM,
				   p.warehouse_id,
				   p.warehouse_item_name,
				   p.warehouse_item_qty,
				   p.warehouse_item_qty_in,
				   p.warehouse_item_qty_out,
				   p.WAREHOUSE_ITEM_ACTIVE,
				   p.WAREHOUSE_ITEM_NOT_ACTIVE,
				   p.warehouse_item_status
			from(
			select house.WAREHOUSE_ID,
				   house.WAREHOUSE_ITEM_NAME,
				   house.WAREHOUSE_ITEM_QTY,
				   house.WAREHOUSE_ITEM_QTY_IN,
				   house.WAREHOUSE_ITEM_QTY_OUT,
				   house.WAREHOUSE_ITEM_STATUS,
				   house.WAREHOUSE_ITEM_ACTIVE,
				   house.WAREHOUSE_ITEM_NOT_ACTIVE
			from fm_warehouse house where 1=1 $whereCond 
			order by house.WAREHOUSE_ITEM_NAME asc)p) $limit
		";
		
		$query = $this->db->query($sql);
        return $query->result_array();
	}
	
	function count_warehouse_item($arr) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_name'])) {
                $whereCond .= " and upper(warehouse_item_name) like upper('%" . $arr['search_by_name'] . "%')";
            }
            if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(warehouse_item_status) = upper('" . $arr['search_by_status'] . "')";
            }            
        }
						
		$sql = "
			select COUNT (*) cnt from ( 
			select rownum RECNUM,
				   p.warehouse_id,
				   p.warehouse_item_name,
				   p.warehouse_item_qty,
				   p.warehouse_item_qty_in,
				   p.warehouse_item_qty_out,
				   p.WAREHOUSE_ITEM_ACTIVE,
				   p.WAREHOUSE_ITEM_NOT_ACTIVE,
				   p.warehouse_item_status
			from(
			select house.WAREHOUSE_ID,
				   house.WAREHOUSE_ITEM_NAME,
				   house.WAREHOUSE_ITEM_QTY,
				   house.WAREHOUSE_ITEM_QTY_IN,
				   house.WAREHOUSE_ITEM_QTY_OUT,
				   house.WAREHOUSE_ITEM_STATUS,
				   house.WAREHOUSE_ITEM_ACTIVE,
				   house.WAREHOUSE_ITEM_NOT_ACTIVE
			from fm_warehouse house where 1=1 $whereCond 
			order by house.WAREHOUSE_ITEM_NAME asc)p)
		";								  
								  
        $result = $this->db->query($sql)->row();
        return $result->CNT;
    }
	
	function update_status_item($arr) {
        $warehouse_id = $arr['warehouse_id'];
        $cond = $arr['cond'];
        
        if (!empty($warehouse_id)) {
            $sql_update = " 
				update fm_warehouse set WAREHOUSE_ITEM_STATUS = '$cond' 
                where WAREHOUSE_ID = '$warehouse_id' 
			";
			
            $this->db->query($sql_update);
        }
    }

	
	function search_warehouse_item($arr, $limit = NULL) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_name'])) {
                $whereCond .= " and upper(warehouse_item_name) like upper('%" . $arr['search_by_name'] . "%')";
            }
            if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(warehouse_item_status) = upper('" . $arr['search_by_status'] . "')";
            }
        }

		$sql = "
			select RECNUM, warehouse_id,
				   warehouse_item_name,
				   warehouse_item_qty,
				   warehouse_item_qty_in,
				   warehouse_item_qty_out,
				   WAREHOUSE_ITEM_ACTIVE,
				   WAREHOUSE_ITEM_NOT_ACTIVE,
				   warehouse_item_status
			from (
			select rownum RECNUM,
				   p.warehouse_id,
				   p.warehouse_item_name,
				   p.warehouse_item_qty,
				   p.warehouse_item_qty_in,
				   p.warehouse_item_qty_out,
				   p.WAREHOUSE_ITEM_ACTIVE,
				   p.WAREHOUSE_ITEM_NOT_ACTIVE,
				   p.warehouse_item_status
			from(
			select house.WAREHOUSE_ID,
				   house.WAREHOUSE_ITEM_NAME,
				   house.WAREHOUSE_ITEM_QTY,
				   house.WAREHOUSE_ITEM_QTY_IN,
				   house.WAREHOUSE_ITEM_QTY_OUT,
				   house.WAREHOUSE_ITEM_STATUS,
				   house.WAREHOUSE_ITEM_ACTIVE,
				   house.WAREHOUSE_ITEM_NOT_ACTIVE
			from fm_warehouse house where 1=1 $whereCond 
			order by house.WAREHOUSE_ITEM_NAME asc)p) $limit
		";
		
		$query = $this->db->query($sql);
        return $query->result_array();
    }
	
	function delete_item($arr) {
		if (!empty($arr['warehouse_id'])) {
			
			$warehouse_id = $arr['warehouse_id'];			
			
			$sql = "
				DELETE FROM fm_warehouse 
				WHERE WAREHOUSE_ID = '$warehouse_id'  				
			";
			
			$query = $this->db->query($sql);
            return true;
		}
	}
	
	function insert_item($arr) {
		$arr['item'] = trim($this->input->post('item'));
		$arr['qty'] = trim($this->input->post('qty'));
		$arr['qty_active'] = trim($this->input->post('qty_active'));
		$arr['qty_not_active'] = trim($this->input->post('qty_not_active'));
		$arr['status'] = trim($this->input->post('status'));		
		
		$pWareItemName = $arr['item'];				
		$pWareItemQty = $arr['qty'];
		$pWareItemActive = $arr['qty_active'];				
		$pWareItemStatus = $arr['status'];
		
		$user = 999999; //$this->session->userdata('ID');
		if ($pWareItemQty == ""){$pWareItemQty ="0";}
		if ($pWareItemActive == ""){$pWareItemActive ="0";}		
		
		$pWareItemNotActive = (int)$pWareItemQty - (int)$pWareItemActive;	
		
		$sql_item = " 
			select max(to_number(substr(warehouse_id,1,5),'99999')) warehouse_id_old, 
			rtrim(to_char(sysdate,'RM')) romawi from fm_warehouse
		";
		$a = $this->db->query($sql_item)->row_array();
		
		$cWareItemIdOld = $a["WAREHOUSE_ID_OLD"];	
		$cRomawi        = $a["ROMAWI"];	
		$cWareItemIdNew = $cWareItemIdOld +1;

		$sql_item2 = "
			select lpad($cWareItemIdNew,5,0)||'/DOC/$cRomawi/'||TO_CHAR(SYSDATE,'YYYY') warehouse_id from dual
		"; 
		$b = $this->db->query($sql_item2)->row_array();
		$cWareItemId = $b["WAREHOUSE_ID"];
					
		$sql_insert_item = "
			insert into fm_warehouse(
				warehouse_id, warehouse_item_name, 
		        warehouse_item_qty, warehouse_item_active, 
				warehouse_item_not_active, warehouse_item_status, 
				usrupd, dtmupd, warehouse_item_qty_in) 
			values (
				'$cWareItemId', '$pWareItemName',
				$pWareItemQty, $pWareItemActive,
				$pWareItemNotActive, '$pWareItemStatus', 
				$user, trunc(sysdate), $pWareItemActive) 
		";
		
		$this->db->query($sql_insert_item);
	}
	
	function update_item() {
		$arr['warehouse_id'] = trim($this->input->post('warehouse_id'));
		$arr['item'] = trim($this->input->post('item'));
		$arr['qty'] = trim($this->input->post('qty'));
		$arr['qty_active'] = trim($this->input->post('qty_active'));
		$arr['qty_not_active'] = trim($this->input->post('qty_not_active'));
		$arr['status'] = trim($this->input->post('status'));		
		
		$pWareItemId = $arr['warehouse_id'];
		$pWareItemName = $arr['item'];				
		$pWareItemQty = $arr['qty'];
		$pWareItemActive = $arr['qty_active'];				
		$pWareItemStatus = $arr['status'];
		
		$user = 999999; //$this->session->userdata('ID');
		if ($pWareItemQty == ""){$pWareItemQty ="0";}
		if ($pWareItemActive == ""){$pWareItemActive ="0";}		
		
		$pWareItemNotActive = (int)$pWareItemQty - (int)$pWareItemActive;				
		
		if (!empty($pWareItemId)) {
			$sql_update = "
				update fm_warehouse set 
				warehouse_item_name = '$pWareItemName', 
				warehouse_item_qty = $pWareItemQty, 
				warehouse_item_active = $pWareItemActive, 
				warehouse_item_not_active = $pWareItemNotActive, 
				warehouse_item_status = '$pWareItemStatus', 
				usrupd = $user, 
				dtmupd = trunc(sysdate) 
				where warehouse_id = '$pWareItemId' 									
			";			
			$this->db->query($sql_update);
		}					
	}
	
	//===============================================================================================================
    // BORROW MODEL ~ SURYA ALAM GULTOM 
    //===============================================================================================================
	
	function list_borrow($arr, $limit) {
		$whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_name'])) {
                $whereCond .= " and upper(roomd_description) like upper('%" . $arr['search_by_name'] . "%')";
            }
			if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(roomd_status) like upper('" . $arr['search_by_status'] . "')";
            }
        }

		$sql = "
			select recnum,
				   room_id,
				   roomd_description,
				   roomd_qty,
				   warehouse_id,
				   roomd_status,
				   roomd_id,
				   room_date
			from (
			select rownum recnum,
				   p.room_id,
				   p.roomd_description,
				   p.roomd_qty,
				   p.warehouse_id,
				   p.roomd_status,
				   p.roomd_id,
				   p.room_date
			from (
			select det.ROOM_ID,
				   det.ROOMD_DESCRIPTION,
				   det.ROOMD_QTY,
				   det.WAREHOUSE_ID,
				   det.ROOMD_STATUS,
				   det.ROOMD_ID, ".
				   // --to_char(room.ROOM_DATE,'DD/MM/YYYY') as 
				 " room.ROOM_DATE
			from FM_MEETING_ROOM_DET det 
			inner join FM_MEETING_ROOM room on det.ROOM_ID = room.ROOM_ID
			where 1=1 $whereCond 
			order by det.ROOM_ID desc)p) $limit 
		";
		
		$query = $this->db->query($sql);
        return $query->result_array();
	}
	
	function count_borrow($arr) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_name'])) {
                $whereCond .= " and upper(roomd_description) like upper('%" . $arr['search_by_name'] . "%')";
            }
			if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(roomd_status) like upper('" . $arr['search_by_status'] . "')";
            }
        }
						
		$sql = "
			select COUNT (*) cnt from ( 
			select rownum recnum,
				   p.room_id,
				   p.roomd_description,
				   p.roomd_qty,
				   p.warehouse_id,
				   p.roomd_status,
				   p.roomd_id,
				   p.room_date
			from (
			select det.ROOM_ID,
				   det.ROOMD_DESCRIPTION,
				   det.ROOMD_QTY,
				   det.WAREHOUSE_ID,
				   det.ROOMD_STATUS,
				   det.ROOMD_ID,
				   to_char(room.ROOM_DATE,'DD/MM/YYYY') as ROOM_DATE
			from FM_MEETING_ROOM_DET det 
			inner join FM_MEETING_ROOM room on det.ROOM_ID = room.ROOM_ID
			where 1=1 $whereCond 
			order by det.ROOM_ID desc)p) 
		";								  
								  
        $result = $this->db->query($sql)->row();
        return $result->CNT;
    }
	
	function search_borrow($arr, $limit = NULL) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_name'])) {
                $whereCond .= " and upper(roomd_description) like upper('%" . $arr['search_by_name'] . "%')";
            }
			if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(roomd_status) like upper('" . $arr['search_by_status'] . "')";
            }
        }

		$sql = "
			select recnum,
				   room_id,
				   roomd_description,
				   roomd_qty,
				   warehouse_id,
				   roomd_status,
				   roomd_id,
				   room_date
			from (
			select rownum recnum,
				   p.room_id,
				   p.roomd_description,
				   p.roomd_qty,
				   p.warehouse_id,
				   p.roomd_status,
				   p.roomd_id,
				   p.room_date
			from (
			select det.ROOM_ID,
				   det.ROOMD_DESCRIPTION,
				   det.ROOMD_QTY,
				   det.WAREHOUSE_ID,
				   det.ROOMD_STATUS,
				   det.ROOMD_ID, ".
				   // --to_char(room.ROOM_DATE,'DD/MM/YYYY') as 
				 " room.ROOM_DATE
			from FM_MEETING_ROOM_DET det 
			inner join FM_MEETING_ROOM room on det.ROOM_ID = room.ROOM_ID
			where 1=1 $whereCond 
			order by det.ROOM_ID desc)p) $limit 
		";
		
		$query = $this->db->query($sql);
        return $query->result_array();
    }
	
	function update_status_order($arr) {
        $roomd_id = $arr['roomd_id'];
        $cond = $arr['cond'];
        
        if (!empty($roomd_id)) {
            $sql_update = " 
				update FM_MEETING_ROOM_DET set ROOMD_STATUS = '$cond' 
                where ROOMD_ID = '$roomd_id' 
			";
			
            $this->db->query($sql_update);
        }
    }
	
	//===============================================================================================================
    // WORK FACILITY MODEL ~ SURYA ALAM GULTOM 
    //===============================================================================================================
	
	function list_work_facility() {
		$sql = "
			select * from fm_req_item_mst where ri_type = 'FASKER' order by ri_name  
		";
		
		$query = $this->db->query($sql);
        return $query->result_array();
	}
	
	function list_room_facility() {
		$sql = "
			select * from fm_req_item_mst where ri_type = 'RUAKER' order by ri_name 	  
		"; 
		
		$query = $this->db->query($sql);
        return $query->result_array();
	}
	
	function add_facility($arr) {		
		if (!empty($arr['name']) && !empty($arr['type'])) {
			
			$ri_name = $arr['name'];
			$ri_type = $arr['type'];
			$ri_desc = $arr['desc'];
			
			$sql = "
				INSERT INTO fm_req_item_mst 
				(ri_name,ri_type,ri_desc)
				VALUES
				('$ri_name','$ri_type','$ri_desc') 
			";
			
			$query = $this->db->query($sql);
            return true;
		}
	}
	
	function edit_facility($arr) {		
		if (!empty($arr['name']) && !empty($arr['type']) && !empty($arr['name_old'])) {
			
			$name_old = $arr['name_old'];
			$ri_name = $arr['name'];
			$ri_type = $arr['type'];
			$ri_desc = $arr['desc'];
			
			$sql = "
				UPDATE fm_req_item_mst SET 
				ri_name = '$ri_name',
				ri_type = '$ri_type',
				ri_desc = '$ri_desc'
				WHERE ri_name = '$name_old' 				
			";
			
			$query = $this->db->query($sql);
            return true;
		}
	}
	
	function delete_facility($arr) {
		if (!empty($arr['name']) && !empty($arr['type'])) {
			
			$ri_name = $arr['name'];
			$ri_type = $arr['type'];
			
			$sql = "
				DELETE FROM fm_req_item_mst 
				WHERE ri_type = '$ri_type'  
				AND ri_name = '$ri_name'  
			";
			
			$query = $this->db->query($sql);
            return true;
		}
	}
	
	//===============================================================================================================
    // JOB POSITION MODEL ~ SURYA ALAM GULTOM 
    //===============================================================================================================
			
	function getRoom($floor_name)
	{
		$sql 	= " SELECT rbf_id, rbf_room
					FROM fm_room_by_floor
					WHERE rbf_floor = '".$floor_name."' 
					ORDER BY rbf_room ASC ";										
				
		$result = $this->db->query($sql);
		$Udata 	= $result->result_Array();
		$Rdata 	= $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function getJobList()
	{
		$sql = "SELECT distinct(JOB) JOB 
				FROM HRS ORDER BY JOB "; 
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}		
		
    function list_work_facility_new($arr) {
		$nama_jabatan = $arr['nama_jabatan'];
		$status_name = $arr['status_name'];
		
		$sql = "
			select ri_name from fm_req_item_mst where ri_type = 'FASKER' and ri_name not in (
			select ri_name from fm_req_item where ri_jabatan_name = '$nama_jabatan' and ri_jabatan_status = '$status_name') 
			order by ri_name 
		"; 
		
		$query = $this->db->query($sql);
        return $query->result_array();
	}
	
	function list_room_facility_new($arr) {
		$nama_jabatan = $arr['nama_jabatan'];
		
		$sql = "
			select ri_name from fm_req_item_mst where ri_type = 'RUAKER' and ri_name not in ( 
			select rui_name as ri_name from fm_req_item_ruang where rui_jabatan_name = '$nama_jabatan' )  
			order by ri_name    
		"; 
		
		$query = $this->db->query($sql);
        return $query->result_array();
	}
	
	function list_fasilitas_request($arr) {
		$nama_jabatan2 = $arr['nama_jabatan2'];
		$floor_name = $arr['floor_name'];
		
		$sql = "
			select * from V_REQ_ITEM 
        	where ri_id not in ( 
				select y.rbl_item_id as ri_id from fm_room_by_location y  
				inner join fm_room_by_floor x ON x.rbf_id = y.rbl_rbf_id  
				where y.rbl_job = '$nama_jabatan2' and x.rbf_floor = '$floor_name' 
			) and ri_jabatan_name = '$nama_jabatan2' 
			and ri_status = 'Y'
			order by ri_name    	
		"; 
		
		$query = $this->db->query($sql);
        return $query->result_array();
	}
	
	function list_data1($arr) {
		$nama_jabatan = $arr['nama_jabatan'];
		$status_name = $arr['status_name'];
			
		if (!empty($nama_jabatan) && !empty($status_name)) {
	
			$sql = " 
				SELECT ri_name, ri_status from fm_req_item
				where ri_jabatan_name = '$nama_jabatan' 
				and ri_jabatan_status = '$status_name'
				order by ri_name 
			";
			
			$query = $this->db->query($sql);
			return $query->result_array();
		} 
	}
	
	function list_data2($arr) {
		$nama_jabatan = $arr['nama_jabatan'];
		$status_name = $arr['status_name'];
			
		if (!empty($nama_jabatan) && !empty($status_name)) {
	
			$sql = " 
				select ri_name, ri_status from v_req_item 
				where ri_jabatan_name = '$nama_jabatan' 
				and ri_jabatan_status = '$status_name'
				and tipe = 'RUAKER' order by ri_name  
			"; 
			
			$query = $this->db->query($sql);
			return $query->result_array();
		} 
	}
	
	function list_data3($arr) {
		$floor_name 	= $arr['floor_name'];
		$nama_jabatan2 	= $arr['nama_jabatan2'];
		$ruang_name		= $arr['ruang_name'];		
			
		if (!empty($floor_name) && !empty($nama_jabatan2)) { //Item Type = FASKER
			if (!empty($ruang_name)) {	
				
				$sql_data = " 
					select b.rbl_item_id as ri_id, c.ri_name, 
					b.rbl_item_tipe as ri_type, 
					b.rbl_item_flag as ri_status, 
					b.rbl_id, b.rbl_job, 
					a.rbf_id, a.rbf_floor, a.rbf_room 
					
					from fm_room_by_floor a, fm_room_by_location b, fm_req_item c   
					
					where a.rbf_id = b.rbl_rbf_id 
					and b.rbl_job = '$nama_jabatan2'
					and a.rbf_room = '$ruang_name'  
          			and b.rbl_item_id = c.ri_id  
					order by c.ri_name 
				";
					
			} else if (empty($ruang_name)) {				
				
				$sql_data = "
					select a.ri_id, a.ri_name, a.tipe as ri_type, b.rbl_item_flag as ri_status, b.rbl_id from v_req_item a 
					inner join fm_room_by_location b ON b.rbl_item_id = a.ri_id
					inner join fm_room_by_floor c ON b.rbl_rbf_id = c.rbf_id   
					where a.ri_jabatan_name = '$nama_jabatan2' and c.rbf_floor = '$floor_name'    
					order by a.tipe, a.ri_name
				";		
			}
			
			$query = $this->db->query($sql_data);
			return $query->result_array();
			
		} 
	}	
	
	function list_data4($arr) {
		$floor_name 	= $arr['floor_name'];
		$nama_jabatan2 	= $arr['nama_jabatan2'];
		$ruang_name		= $arr['ruang_name'];		
			
		if (!empty($floor_name) && !empty($nama_jabatan2)) { //Item Type = RUAKER
			if (!empty($ruang_name)) {	
				
				$sql_data = " 
					select b.rbl_item_id as ri_id, d.rui_name as ri_name, 
					b.rbl_item_tipe as ri_type, 
					b.rbl_item_flag as ri_status, 
					b.rbl_id, b.rbl_job, 
					a.rbf_id, a.rbf_floor, a.rbf_room 
					
					from fm_room_by_floor a, fm_room_by_location b, fm_req_item_ruang d 
					
					where a.rbf_id = b.rbl_rbf_id 
					and b.rbl_job = '$nama_jabatan2'
					and a.rbf_room = '$ruang_name'  
          			and b.rbl_item_id = d.rui_id
					order by d.rui_name 
				";
					
			} else {		
				
				$sql_data = "
					select a.ri_id, a.ri_name, a.tipe as ri_type, b.rbl_item_flag as ri_status from v_req_item a 
					inner join fm_room_by_location b ON b.rbl_item_id = a.ri_id
					inner join fm_room_by_floor c ON b.rbl_rbf_id = c.rbf_id   
					where a.ri_jabatan_name = '$nama_jabatan2' and c.rbf_floor = '$floor_name'    
					order by a.tipe, a.ri_name
				";			
			}
			
			$query = $this->db->query($sql_data);
			return $query->result_array();
			
		} 
	}	
		
	function add_data1($arr) {
		$nama_jabatan = $arr['nama_jabatan'];
		$status_name = $arr['status_name'];
		$work_awal = $arr['work_awal'];
		$status_awal = $arr['status_awal'];
		
		if (!empty($arr['work_awal']) && !empty($arr['status_awal'])) {
			
			$sql = "
				insert into fm_req_item 
				(ri_name, ri_jabatan_name, ri_jabatan_status, ri_status) 
				values 
				('$work_awal','$nama_jabatan','$status_name','$status_awal') 
			";
			
			$query = $this->db->query($sql);
            return true;
		}
	}
	
	function add_data2($arr) {
		$nama_jabatan = $arr['nama_jabatan'];
		$status_name = $arr['status_name'];
		$work2_awal = $arr['work2_awal'];
		$status2_awal = $arr['status2_awal'];
		
		if (!empty($arr['work2_awal']) && !empty($arr['status2_awal'])) {
			
			$sql = "
				insert into fm_req_item_ruang 
				(rui_name, rui_jabatan_name, rui_jabatan_status, rui_status) 
				values 
				('$work2_awal','$nama_jabatan','$status_name','$status2_awal') 
			";
			
			$query = $this->db->query($sql);
            return true;
		}
	}
	
	function add_data3($arr) {
		$floor_name 	= $arr['floor_name'];
		$nama_jabatan2 	= $arr['nama_jabatan2'];
		$new_room 		= $arr['new_room'];
		$work_awal3 	= $arr['work_awal3'];
		$status_awal3 	= $arr['status_awal3'];
		$type_save 		= $arr['type_save'];
		
		if (!empty($arr['floor_name']) && !empty($arr['new_room']) && $arr['type_save'] == 1) {
			
			$sql = "
				insert into fm_room_by_floor  
				(rbf_floor,rbf_room) 
				values 
				($floor_name,'$new_room') 
			";
			
			$query = $this->db->query($sql);
        
		}
		
		$sql_1 = "SELECT MAX (rbf_id) rbf_id_max
                    FROM fm_room_by_floor 
                 WHERE rbf_floor = '$floor_name' and rbf_room = '$new_room' ";
				
		$query1 = $this->db->query($sql_1)->row_array();
        $rbf_id = intval($query1['RBF_ID_MAX']);
		
		if (!empty($arr['work_awal3']) && !empty($arr['status_awal3'])) {
			
			$sql = "
				insert into fm_room_by_location  
				(rbl_rbf_id,rbl_job,rbl_item_id,rbl_item_tipe,rbl_item_flag) 
				values 
				($rbf_id,'$nama_jabatan2',$work_awal3,'SURYA','$status_awal3') 
			";
			
			$query = $this->db->query($sql);          	
		
			$sql_2 = "SELECT tipe as ri_type from v_req_item where ri_id = '$work_awal3' and ri_jabatan_name = '$nama_jabatan2' ";
			
			$query2 = $this->db->query($sql_2)->row_array();
			$hasil = $query2['RI_TYPE'];
			
			$sql = "
				UPDATE fm_room_by_location set 
					rbl_item_tipe = '$hasil' 
				WHERE 
					rbl_rbf_id = $rbf_id 
				and 
					rbl_item_id = $work_awal3  
			";
			
			$query = $this->db->query($sql);
			return true;
		
		}
	}
	
	function edit_data1($arr) {		
		if (!empty($arr['ri_name']) && !empty($arr['ri_jabatan_name']) && !empty($arr['ri_jabatan_status']) && !empty($arr['ri_status'])) {

			$ri_name 			= $arr['ri_name'];
			$ri_jabatan_name 	= $arr['ri_jabatan_name']; 
			$ri_jabatan_status	= $arr['ri_jabatan_status'];
			$ri_status 			= $arr['ri_status'];
			
			$sql = " 
				Update fm_req_item set 
					ri_status			= '$ri_status'
				where 
					ri_name 			= '$ri_name'
				and
					ri_jabatan_name		= '$ri_jabatan_name'
				and
					ri_jabatan_status	= '$ri_jabatan_status' 
			";
			
			$query = $this->db->query($sql);
			return true;
		}
	}
	
	function edit_data2($arr) {
		if (!empty($arr['rui_name']) && !empty($arr['rui_jabatan_name']) && !empty($arr['rui_jabatan_status']) && !empty($arr['rui_status'])) {

			$rui_name 			= $arr['rui_name'];
			$rui_jabatan_name 	= $arr['rui_jabatan_name']; 
			$rui_jabatan_status	= $arr['rui_jabatan_status'];
			$rui_status 		= $arr['rui_status'];
			
			$sql = " 
				Update fm_req_item_ruang set 
					rui_status			= '$rui_status'
				where 
					rui_name 			= '$rui_name'
				and
					rui_jabatan_name	= '$rui_jabatan_name'
				and
					rui_jabatan_status	= '$rui_jabatan_status' 
			";
			
			$query = $this->db->query($sql);
			return true;
		}
	}
	
	function edit_data3($arr) {
		if (!empty($arr['rbl_job']) && !empty($arr['rbl_item_id']) && !empty($arr['rbl_item_tipe']) && !empty($arr['rbl_item_flag'])) {

			$rbl_job 		= $arr['rbl_job'];
			$rbl_item_id 	= $arr['rbl_item_id'];
			$rbl_item_tipe 	= $arr['rbl_item_tipe']; 
			$rbl_item_flag 	= $arr['rbl_item_flag'];
			
			$sql = " 
				Update fm_room_by_location set 
					rbl_item_flag	= '$rbl_item_flag' 
				where  
					rbl_item_id 	= '$rbl_item_id' 
				and 
					rbl_item_tipe	= '$rbl_item_tipe' 
				and 
					rbl_job			= '$rbl_job' 
			";
			
			$query = $this->db->query($sql);
			return true;
		}
	}
	
	function delete_data1($arr) {
		if (!empty($arr['ri_name']) && !empty($arr['ri_jabatan_name']) && !empty($arr['ri_jabatan_status'])) {

			$ri_name 			= $arr['ri_name'];
			$ri_jabatan_name 	= $arr['ri_jabatan_name']; 
			$ri_jabatan_status	= $arr['ri_jabatan_status'];
			
			$sql = " 
				Delete from fm_req_item 
				where 
					ri_name 			= '$ri_name'
				and
					ri_jabatan_name		= '$ri_jabatan_name'
				and
					ri_jabatan_status	= '$ri_jabatan_status' 
			";
			
			$query = $this->db->query($sql);
			return true;
		}
	}
	
	function delete_data2($arr) {
		if (!empty($arr['rui_name']) && !empty($arr['rui_jabatan_name']) && !empty($arr['rui_jabatan_status'])) {

			$rui_name 			= $arr['rui_name'];
			$rui_jabatan_name 	= $arr['rui_jabatan_name']; 
			$rui_jabatan_status	= $arr['rui_jabatan_status'];
			
			$sql = " 
				Delete from fm_req_item_ruang 
				where 
					rui_name 			= '$rui_name'
				and
					rui_jabatan_name	= '$rui_jabatan_name'
				and
					rui_jabatan_status	= '$rui_jabatan_status' 
			";
			
			$query = $this->db->query($sql);
			return true;
		}
	}
	
	function delete_data3($arr) {
		if (!empty($arr['rbl_id'])) {
			$rbl_id 			= $arr['rbl_id'];
			
			$sql = " 
				Delete from fm_room_by_location 
				where 
					rbl_id 			= '$rbl_id'
			";
			
			$query = $this->db->query($sql);
			return true;
		}
	}
	
	//=======================================================================================================================
    // CONTRACT MODEL ~ SURYA ALAM GULTOM 
    //=======================================================================================================================

    function count_contract($arr) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_id'])) {
                $whereCond .= " and upper(doc_id) like upper('%" . $arr['search_by_id'] . "%')";
            }
            if (!empty($arr['search_by_no'])) {
                $whereCond .= " and upper(doc_contract_no) like upper('%" . $arr['search_by_no'] . "%')";
            }
            if (!empty($arr['search_by_title'])) {
                $whereCond .= " and upper(doc_title) like upper('%" . $arr['search_by_title'] . "%')";
            }
			if (!empty($arr['search_by_vendor'])) {
                $whereCond .= " and upper(b.vendor_name) like upper('%" . $arr['search_by_vendor'] . "%')";
            }
        }
		
		$sql = "
			select COUNT (*) cnt from ( 
			select doc_induk, doc_id, doc_contract_no, doc_title, doc_sdate, doc_edate, doc_file, vendor_name, vendor_id, rownum recnum from ( 
			select a.doc_induk, a.doc_id, a.doc_contract_no, a.doc_title, a.doc_sdate, a.doc_edate, a.doc_file, b.vendor_name, b.vendor_id 
			from fm_mst_document a, lkk_vendor_mst b where a.doc_vendor_id = b.vendor_id $whereCond )) 		
		";								  
								  
        $result = $this->db->query($sql)->row();
        return $result->CNT;
    }
	
	function list_contract($arr, $limit = NULL) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_id'])) {
                $whereCond .= " and upper(doc_id) like upper('%" . $arr['search_by_id'] . "%')";
            }
            if (!empty($arr['search_by_no'])) {
                $whereCond .= " and upper(doc_contract_no) like upper('%" . $arr['search_by_no'] . "%')";
            }
            if (!empty($arr['search_by_title'])) {
                $whereCond .= " and upper(doc_title) like upper('%" . $arr['search_by_title'] . "%')";
            }
			if (!empty($arr['search_by_vendor'])) {
                $whereCond .= " and upper(b.vendor_name) like upper('%" . $arr['search_by_vendor'] . "%')";
            }
        }

        $sql = "
			select doc_induk, doc_id, doc_contract_no, doc_title, doc_sdate, doc_edate, doc_file, vendor_name, vendor_id, recnum from ( 
			select doc_induk, doc_id, doc_contract_no, doc_title, doc_sdate, doc_edate, doc_file, vendor_name, vendor_id, rownum recnum from ( 
			select a.doc_induk, a.doc_id, a.doc_contract_no, a.doc_title, a.doc_sdate, a.doc_edate, a.doc_file, b.vendor_name, b.vendor_id  
			from fm_mst_document a, lkk_vendor_mst b where a.doc_vendor_id = b.vendor_id $whereCond 
			order by a.doc_id asc)) $limit 			
		";
        
		$query = $this->db->query($sql);
        return $query->result_array();
    }
	
	function contract_list_updated($arr) {
        if (!empty($arr['doc_id'])) {
        
			$sql = "
				select a.doc_induk, a.doc_id, a.doc_contract_no, a.doc_title, a.doc_sdate, a.doc_edate, a.doc_file, a.doc_description, 
				a.doc_reminder, b.vendor_name, b.vendor_id from fm_mst_document a, lkk_vendor_mst b 
				where a.doc_vendor_id = b.vendor_id 				
				and upper(doc_id) = upper('" . $arr['doc_id'] . "') 
			";					

			$query = $this->db->query($sql);
            return $query->row_array();
        }
    }
	
	function contract_table_updated($arr) {
		if (!empty($arr['doc_id'])) {
        
			$sql = "
				select det_id, det_type_price, det_doc_id, det_price, det_satuan, det_price_unit, det_qty_awal, det_qty_akhir, det_penagihan, type_price_desc, recnum from ( 
				select det_id, det_type_price, det_doc_id, det_price, det_satuan, det_price_unit, det_qty_awal, det_qty_akhir, det_penagihan, type_price_desc, rownum recnum from (
				select a.det_id, a.det_type_price, a.det_doc_id, a.det_price, a.det_satuan, a.det_price_unit, a.det_qty_awal, a.det_qty_akhir, a.det_penagihan, c.type_price_desc  
				from fm_det_document a, fm_mst_document b, fm_mst_type_price c  
				where a.det_doc_id = b.doc_id and c.type_price_id = a.det_type_price   				
				and upper(a.det_doc_id) = upper('" . $arr['doc_id'] . "') order by det_type_price  
				))
			";					

			$query = $this->db->query($sql);
            return $query->result_array();
        }
	}
	
	function edit_data_table_list($arr) {
		if (!empty($arr['det_id'])) {
			$sql = " 
				select * from fm_det_document a 
				inner join fm_mst_type_price b ON b.type_price_id = a.det_type_price 
				where upper(a.det_id) = upper('" . $arr['det_id'] . "') 		
			"; 
			
			$query = $this->db->query($sql);
            return $query->row_array();
		}
	}
	
	function search_contract($arr, $limit = NULL) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_id'])) {
                $whereCond .= " and upper(doc_id) like upper('%" . $arr['search_by_id'] . "%')";
            }
            if (!empty($arr['search_by_no'])) {
                $whereCond .= " and upper(doc_contract_no) like upper('%" . $arr['search_by_no'] . "%')";
            }
            if (!empty($arr['search_by_title'])) {
                $whereCond .= " and upper(doc_title) like upper('%" . $arr['search_by_title'] . "%')";
            }
			if (!empty($arr['search_by_vendor'])) {
                $whereCond .= " and upper(b.vendor_name) like upper('%" . $arr['search_by_vendor'] . "%')";
            }
        }

        $sql = "
			select doc_induk, doc_id, doc_contract_no, doc_title, doc_sdate, doc_edate, doc_file, vendor_name, vendor_id, recnum from ( 
			select doc_induk, doc_id, doc_contract_no, doc_title, doc_sdate, doc_edate, doc_file, vendor_name, vendor_id, rownum recnum from ( 
			select a.doc_induk, a.doc_id, a.doc_contract_no, a.doc_title, a.doc_sdate, a.doc_edate, a.doc_file, b.vendor_name, b.vendor_id  
			from fm_mst_document a, lkk_vendor_mst b where a.doc_vendor_id = b.vendor_id $whereCond 
			order by a.doc_contract_no asc)) $limit		
		";
        		
		$query = $this->db->query($sql);
        return $query->result_array();
    }
	
	function getTypePriceList()
	{
		$sql = "SELECT
				TYPE_PRICE_ID, TYPE_PRICE_DESC FROM fm_mst_type_price 
				ORDER BY TYPE_PRICE_DESC ";
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function getTypeSatuanList()
	{
		$sql = "SELECT
				TYPE_SATUAN_NAME FROM fm_mst_type_satuan 
				ORDER BY TYPE_SATUAN_NAME ";
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function getVendorList()
	{
		$sql = "SELECT VENDOR_ID, VENDOR_NAME 
				FROM LKK_VENDOR_MST 
				ORDER BY VENDOR_NAME "; 
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function insert_data_contract($arr) {
		$vendor_id = $arr['vendor_id'];
		$doc_no = $arr['doc_no'];
		$doc_title = $arr['doc_title'];
		$date1 = $arr['date1'];
		$date2 = $arr['date2'];
		$description = $arr['description'];
		$reminder = $arr['reminder'];
		$nama_file = $arr['nama_file'];   
		
		$filesize  = $arr['filesize'];
		$filetype  = $arr['filetype'];
		
		if ($vendor_id == "0"){$vendor_id = "";}
		
		if (!empty($vendor_id)) {
		
			//$tempfile = $_FILES["file"]["name"];
			//move_uploaded_file($_FILES["file"]["tmp_name"],
			//"../upload/" . $_FILES["file"]["name"]);
			//echo "Stored in: " . "upload/" . $_FILES["file"]["name"];
			
			$qry1 = " select max(to_number(substr(doc_id,1,5),'99999')) doc_id_old, rtrim(to_char(sysdate,'RM')) romawi from fm_mst_document ";
			$a = $this->db->query($qry1)->row_array();
			
			$cNoDocIdOld = $a["DOC_ID_OLD"];	
			$cRomawi     = $a["ROMAWI"];	
			$cNoDocIdNew = $cNoDocIdOld +1;
						
			$qry2 = " select lpad($cNoDocIdNew,5,0)||'/DOC/$cRomawi/'||TO_CHAR(SYSDATE,'YYYY') no_doc_id from dual ";
			$b = $this->db->query($qry2)->row_array();
			
			$cNoDocId = $b["NO_DOC_ID"];			
						
			$sql_stmt =	"
				insert into fm_mst_document (
					doc_reminder, 
					doc_id, 
					doc_induk, 
					doc_title, 
					doc_date, 
					doc_description, 
					doc_file, 
					doc_sdate, 
					doc_edate, 
					doc_vendor_id, 
					doc_contract_no, 
					doc_type, 
					doc_size  
				) values ( 				
					'$reminder',
					'$cNoDocId',
					'$cNoDocId',
					'$doc_title',
					trunc(sysdate),
					'$description',
					'$nama_file',
					to_date('$date1','dd/mm/yyyy'),
					to_date('$date2','dd/mm/yyyy'),
					'$vendor_id',
					'$doc_no',
					'$filetype',
					'$filesize' 
				)
			";
			
			$this->db->query($sql_stmt);
			
			$type = $arr['type'];
			$satuan = $arr['satuan'];
			$qty_awal = $arr['qty_awal'];
			$qty_akhir = $arr['qty_akhir'];
			$currency = $arr['currency'];
			$price = $arr['price'];
			$penagihan = $arr['penagihan'];
			
			if (!empty($type)) {
			
				$qry4 =	" insert into fm_det_document (
							det_type_price, 
							det_price, 
							det_doc_id, 
							det_price_unit, 
							det_satuan, 
							det_qty_awal, 
							det_qty_akhir, 
							det_penagihan
						) values (
							$type, 
							$price, 
							'$cNoDocId',
							'$currency',
							'$satuan',
							$qty_awal,
							$qty_akhir,
							'$penagihan' 
						) 
				";
		
				$this->db->query($qry4);
			}
			
		}
	}
	
	function update_data_contract($arr) {
		$doc_induk = $arr['doc_induk'];
		$doc_id = $arr['doc_id'];		
		$status_id = $arr['status_id'];
		
		$vendor_id = $arr['vendor_id'];
		$doc_no = $arr['doc_no'];
		$doc_title = $arr['doc_title'];
		$date1 = $arr['date1'];
		$date2 = $arr['date2'];
		$description = $arr['description'];
		$reminder = $arr['reminder'];
		$nama_file = $arr['nama_file'];   
		
		$filesize  = $arr['filesize'];
		$filetype  = $arr['filetype'];
		
		if ($vendor_id == "0"){$vendor_id = "";}
		
		if (!empty($vendor_id)) {
			
			if ($status_id == "Edit"){
				if(!empty($nama_file)){
					$sql_update_data_contract = " 
						update fm_mst_document set 
							 doc_contract_no = '$doc_no', 
							 doc_vendor_id	 = '$vendor_id', 
							 doc_title	     = '$doc_title', 
							 doc_description = '$description', 
							 doc_date	     = trunc(sysdate), 
							 doc_file	     = '$nama_file',								 
							 doc_size        = '$filesize', 
							 doc_type        = '$filetype', 
							 doc_sdate	     =	to_date('$date1','dd/mm/yyyy'),	
							 doc_edate	     =	to_date('$date2','dd/mm/yyyy'), 
							 doc_reminder    = '$reminder' 
						where doc_id = '$doc_id'						
					";
				} else {
					$sql_update_data_contract = " 
						update fm_mst_document set 
							 doc_contract_no = '$doc_no', 
							 doc_vendor_id	 = '$vendor_id', 
							 doc_title	     = '$doc_title', 
							 doc_description = '$description', 
							 doc_date	     = trunc(sysdate), 
							 doc_sdate	     =	to_date('$date1','dd/mm/yyyy'),	
							 doc_edate	     =	to_date('$date2','dd/mm/yyyy'), 
							 doc_reminder    = '$reminder' 
						where doc_id = '$doc_id'						
					";
				}
			} else if ($status_id == "Revise"){
					$qry_revisi = " select max(doc_revisi) max_revisi from fm_mst_document where doc_induk = '$doc_induk' ";
					$a = $this->db->query($qry_revisi)->row_array();
					
					$vMaxRevisi     = $a["MAX_REVISI"];	
						
					if(	$vMaxRevisi == null || empty($vMaxRevisi)){
						$vMaxRevisi  = 1;
					}else{
						$vMaxRevisi = ($vMaxRevisi + 1);
					}
							
					$sql_update_data_contract = " 
						insert into fm_mst_document (
							doc_reminder, 
							doc_revisi, 
							doc_id, 
							doc_induk, 
							doc_title, 
							doc_date, 
							doc_description, 
							doc_file, 
							doc_sdate, 
							doc_edate, 
							doc_vendor_id, 
							doc_contract_no, 
							doc_type, 
							doc_size
						) values (  
							'$reminder', 
							'$vMaxRevisi', 
							'$doc_induk-$vMaxRevisi', 
							'$doc_induk', 
							'$doc_title', 
							trunc(sysdate), 
							'$description', 
							'$nama_file', 
							to_date('$date1','dd/mm/yyyy'), 
							to_date('$date2','dd/mm/yyyy'), 
							'$vendor_id', 
							'$doc_no', 
							'$filetype', 
							'$filesize' 
						)
					";
			}
			
			$this->db->query($sql_update_data_contract);
			
			//============================== TABLE = PRICE ==============================
			
			$det_id = "";
			$type = $arr['type'];
			$satuan = $arr['satuan'];
			$qty_awal = $arr['qty_awal'];
			$qty_akhir = $arr['qty_akhir'];
			$currency = $arr['currency'];
			$price = $arr['price'];
			$penagihan = $arr['penagihan'];
			
			if ($type == "0"){$type = "";}
			
			if (!empty($type)) {
				if ($status_id == "Edit"){
				   
					$qry7 = "
						insert into fm_det_document ( 
								det_type_price, 
								det_price, 
								det_doc_id, 
								det_price_unit, 
								det_satuan, 
								det_qty_awal, 
								det_qty_akhir, 
								det_penagihan 
						) values ( 
								'$type',
								'$price',
								'$doc_id',
								'$currency',
								'$satuan',
								'$qty_awal',
								'$qty_akhir',
								'$penagihan' 
						) 
					";
					
				} else if ($status_id == "Revise"){
				
					$qry7 = "
						insert into fm_det_document ( 
								det_type_price, 
								det_price, 
								det_doc_id, 
								det_price_unit, 
								det_satuan, 
								det_qty_awal, 
								det_qty_akhir, 
								det_penagihan 
						) values ( 
								'$type',
								'$price',
								'$$doc_induk-$vMaxRevisi',
								'$currency',
								'$satuan',
								'$qty_awal',
								'$qty_akhir',
								'$penagihan' 
						)
					";
				
				}
				
				$this->db->query($qry7);
			}
		}		
	}
	
	function delete_data_contract($arr) {
		if (!empty($arr['doc_id'])) {
			
			$doc_id = $arr['doc_id'];
			
			$sql = "
				DELETE FROM FM_MST_DOCUMENT  
				WHERE DOC_ID = '$doc_id' 
			";
			
			$query = $this->db->query($sql);
			
			$sql_tbl = "
				DELETE FROM FM_DET_DOCUMENT  
				WHERE DET_DOC_ID = '$doc_id' 
			";
			
			$query = $this->db->query($sql_tbl);
			
            return true;
		}
	}
	
	function insert_data_table($arr) {
		$doc_id = $arr['doc_id'];
		$type = $arr['type'];
		$satuan = $arr['satuan'];
		$qty_awal = $arr['qty_awal'];
		$qty_akhir = $arr['qty_akhir'];
		$currency = $arr['currency'];
		$price = $arr['price'];
		$penagihan = $arr['penagihan'];
		
		if (!empty($type)) {
		
			$qry9 =	" insert into fm_det_document (
						det_type_price, 
						det_price, 
						det_doc_id, 
						det_price_unit, 
						det_satuan, 
						det_qty_awal, 
						det_qty_akhir, 
						det_penagihan
					) values (
						$type, 
						$price, 
						'$doc_id',
						'$currency',
						'$satuan',
						$qty_awal,
						$qty_akhir,
						'$penagihan' 
					) 
			";
	
			$this->db->query($qry9);
		}
	}
	
	function update_data_table($arr) {
		$det_id = $arr['det_id'];
		$det_type_price = $arr['det_type_price'];
		
		$type = $arr['type'];
		$satuan = $arr['satuan'];
		$qty_awal = $arr['qty_awal'];
		$qty_akhir = $arr['qty_akhir'];
		$currency = $arr['currency'];
		$price = $arr['price'];
		$penagihan = $arr['penagihan'];
		
		if (!empty($type)) {
		
			$qry9 =	" 
						update fm_det_document set 
								det_type_price = '$type', 
							    det_price      = '$price', 
							    det_price_unit = '$currency', 
							    det_satuan     = '$satuan', 
							    det_qty_awal   = '$qty_awal',  
							    det_qty_akhir  = '$qty_akhir',  
							    det_penagihan  = '$penagihan'  
						where det_id = '$det_id' and det_type_price = '$det_type_price'   
			";
	
			$this->db->query($qry9);
		}
	}
	
	//=======================================================================================================================
    // ROOM MODEL ~ SURYA ALAM GULTOM 
    //=======================================================================================================================

    function count_room($arr) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_room'])) {
                $whereCond .= " and upper(mst_category_desc) like upper('%" . $arr['search_by_room'] . "%')";
            }
            if (!empty($arr['search_by_floor'])) {
                $whereCond .= " and upper(mst_floor_desc) = upper('" . $arr['search_by_floor'] . "')";
            }
            if (!empty($arr['search_by_type'])) {
                $whereCond .= " and a.mst_type_id = '" . $arr['search_by_type'] . "' ";
            }
        }

        $sql = "
			select COUNT (*) cnt from ( 
			
			select mst_type_id, mst_type_desc, mst_category_id, mst_category_desc, 
			mst_floor_id, mst_floor_desc, mst_category_status, rownum recnum from ( 

			select a.mst_type_id, a.mst_type_desc, b.mst_category_id, b.mst_category_desc, 
			c.mst_floor_id, c.mst_floor_desc, b.mst_category_status     
			from fm_mst_type a, fm_mst_category b, fm_mst_floor c 
			where a.mst_type_id         = b.mst_type_id and 
				  a.mst_type_id         = c.mst_type_id and 
				  b.mst_floor_id        = c.mst_floor_id and 
				  a.mst_type_status     = 'Y' and 
				  c.mst_floor_status    = 'Y' $whereCond
			order by a.mst_type_desc asc, b.mst_category_desc asc, c.mst_floor_desc asc )) 			
		";								  
								  
        $result = $this->db->query($sql)->row();
        return $result->CNT;
    }
	
	function list_room($arr, $limit = NULL) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_room'])) {
                $whereCond .= " and upper(mst_category_desc) like upper('%" . $arr['search_by_room'] . "%')";
            }
            if (!empty($arr['search_by_floor'])) {
                $whereCond .= " and upper(mst_floor_desc) = upper('" . $arr['search_by_floor'] . "')";
            }
            if (!empty($arr['search_by_type'])) {
                $whereCond .= " and a.mst_type_id = '" . $arr['search_by_type'] . "' ";
            }
        }

        $sql = "
			select mst_type_id, mst_type_desc, mst_category_id, mst_category_desc, 
			mst_floor_id, mst_floor_desc, mst_category_status, recnum from ( 
			
			select mst_type_id, mst_type_desc, mst_category_id, mst_category_desc, 
			mst_floor_id, mst_floor_desc, mst_category_status, rownum recnum from ( 

			select a.mst_type_id, a.mst_type_desc, b.mst_category_id, b.mst_category_desc, 
			c.mst_floor_id, c.mst_floor_desc, b.mst_category_status     
			from fm_mst_type a, fm_mst_category b, fm_mst_floor c 
			where a.mst_type_id         = b.mst_type_id and 
				  a.mst_type_id         = c.mst_type_id and 
				  b.mst_floor_id        = c.mst_floor_id and 
				  a.mst_type_status     = 'Y' and 
				  c.mst_floor_status    = 'Y' $whereCond
			order by a.mst_type_desc asc, b.mst_category_desc asc, c.mst_floor_desc asc )) 
			
			$limit		
		";
        
		$query = $this->db->query($sql);
        return $query->result_array();
    }
	
	function getFloorList()
	{
		$sql = "SELECT DISTINCT MST_FLOOR_DESC FROM FM_MST_FLOOR 
				WHERE MST_FLOOR_STATUS = 'Y' ORDER BY MST_FLOOR_DESC "; 
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function getTypeList()
	{
		$sql = "SELECT
				MST_TYPE_ID, MST_TYPE_DESC FROM FM_MST_TYPE 
				WHERE MST_TYPE_STATUS = 'Y' ORDER BY MST_TYPE_DESC ";
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function view_detail_room($arr) {
		if (!empty($arr['room_id']) && !empty($arr['type'])) {
			
			$cCategId = $arr['room_id'];
			$cTypeId = $arr['type'];
			
			$sql = "
				SELECT a.ITEM_PUBLIC_ROOM_ID, upper(b.ITEM_PUBLIC_ROOM_NAME) ITEM_PUBLIC_ROOM_NAME,
					   b.ITEM_PUBLIC_ROOM_STATUS, a.ITEM_PUBLIC_ROOM_QTY ,
					   a.ITEM_PUBLIC_ROOM_UOM					   					   
				FROM FM_MST_ITEM_CATEGORY a 
				INNER JOIN FM_MST_ITEM_PUBLIC_ROOM b ON a.ITEM_PUBLIC_ROOM_ID = b.ITEM_PUBLIC_ROOM_ID 
				WHERE a.MST_CATEGORY_ID = $cCategId AND a.MST_TYPE_ID = $cTypeId 
				AND b.ITEM_PUBLIC_ROOM_STATUS = 'Y' 
				ORDER BY ITEM_PUBLIC_ROOM_NAME
			";
			
			$query = $this->db->query($sql);
            return $query->result_array();
		}
	}
	
	function delete_master_item($arr) {
		if (!empty($arr['public_room_id'])) {
			$cPublicId = $arr['public_room_id'];
			// $sql = "
				// DELETE FROM FM_MST_ITEM_PUBLIC_ROOM 
				// WHERE ITEM_PUBLIC_ROOM_ID = '$cPublicId' 
			// ";
			$sql = "
				UPDATE FM_MST_ITEM_PUBLIC_ROOM SET 			
				ITEM_PUBLIC_ROOM_STATUS	= 'N' 
				WHERE ITEM_PUBLIC_ROOM_ID = '$cPublicId' 
			";
			$query = $this->db->query($sql);
            return true;
		}
	}
	
	function delete_master_room($arr) {
		if (!empty($arr['room_id'])) {
			$room_id = $arr['room_id'];
			$sql = "
				DELETE FROM FM_MST_CATEGORY 
				WHERE MST_CATEGORY_ID = '$room_id' 
			";
			//$sql = "
//				UPDATE FM_MST_CATEGORY SET 			
//				MST_CATEGORY_STATUS = 'N' 
//				WHERE MST_CATEGORY_ID = '$room_id' 
//			";
			$query = $this->db->query($sql);
            return true;
		}
	}
	
	function delete_detail_room($arr) {
		if (!empty($arr['room_id']) && !empty($arr['type']) && !empty($arr['public_room_id'])) {
			
			$cCategId = $arr['room_id'];
			$cTypeId = $arr['type'];
			$cPublicId = $arr['public_room_id'];
			
			$sql = "
				DELETE FROM FM_MST_ITEM_CATEGORY 
				WHERE MST_CATEGORY_ID = $cCategId  
				AND MST_TYPE_ID = $cTypeId  
				AND ITEM_PUBLIC_ROOM_ID = '$cPublicId' 
			";
			
			$query = $this->db->query($sql);
            return true;
		}
	}
	
	function view_master_item() {
		$sql = "
			select room.ITEM_PUBLIC_ROOM_ID ,
				upper(room.ITEM_PUBLIC_ROOM_NAME) ITEM_PUBLIC_ROOM_NAME,
				room.ITEM_PUBLIC_ROOM_STATUS ,
				room.ITEM_PUBLIC_ROOM_QTY ,
				room.ITEM_PUBLIC_ROOM_UOM 
			from FM_MST_ITEM_PUBLIC_ROOM room 			
			ORDER BY ITEM_PUBLIC_ROOM_ID 
		";
		
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	
	function edit_master_item($arr) {		
		$public_room_name = $arr['item'];
		$public_room_qty = $arr['quantity'];
		$public_room_uom = $arr['uom'];
		$status = $arr['status'];	
		$public_room_id = $arr['public_room_id'];	
		
		$sql = "
			UPDATE FM_MST_ITEM_PUBLIC_ROOM SET 
			ITEM_PUBLIC_ROOM_NAME = '$public_room_name', 
			ITEM_PUBLIC_ROOM_QTY = $public_room_qty, 
			ITEM_PUBLIC_ROOM_UOM = '$public_room_uom', 
			ITEM_PUBLIC_ROOM_STATUS	= '$status' 
			WHERE 
			ITEM_PUBLIC_ROOM_ID = '$public_room_id' 
		";
		
		$query = $this->db->query($sql);
		return true;
	}
	
	function edit_master_view($arr) {
		if (!empty($arr['public_room_id'])) {
			
			$cPublicId = $arr['public_room_id'];
			
			$sql = "
				select room.ITEM_PUBLIC_ROOM_ID ,
					upper(room.ITEM_PUBLIC_ROOM_NAME) ITEM_PUBLIC_ROOM_NAME,
					room.ITEM_PUBLIC_ROOM_STATUS ,
					room.ITEM_PUBLIC_ROOM_QTY ,
					room.ITEM_PUBLIC_ROOM_UOM 
				from FM_MST_ITEM_PUBLIC_ROOM room				
				WHERE ITEM_PUBLIC_ROOM_ID = '$cPublicId' 
				ORDER BY ITEM_PUBLIC_ROOM_ID 
			";
			
			$query = $this->db->query($sql);
			return $query->row_array();
		}
	}
	
	function add_detail_room($arr) {
		if (!empty($arr['room_id']) && !empty($arr['type'])) {
			
			$cCategId = $arr['room_id'];
			$cTypeId = $arr['type'];
			
			$sql = "
				select room.ITEM_PUBLIC_ROOM_ID ,
					upper(room.ITEM_PUBLIC_ROOM_NAME) ITEM_PUBLIC_ROOM_NAME,
					room.ITEM_PUBLIC_ROOM_STATUS ,
					room.ITEM_PUBLIC_ROOM_QTY ,
					room.ITEM_PUBLIC_ROOM_UOM 
				from FM_MST_ITEM_PUBLIC_ROOM room
				where room.ITEM_PUBLIC_ROOM_ID not in (        
					select categ.ITEM_PUBLIC_ROOM_ID
					from FM_MST_ITEM_CATEGORY categ
					where categ.MST_CATEGORY_ID = $cCategId
					and categ.MST_TYPE_ID = $cTypeId ) 
				AND room.ITEM_PUBLIC_ROOM_STATUS = 'Y' 
				ORDER BY ITEM_PUBLIC_ROOM_ID 
			";
			
			$query = $this->db->query($sql);
            return $query->result_array();
		}
	}
	
	function list_data_detail($arr) {
		if (!empty($arr['room_id']) && !empty($arr['type']) && !empty($arr['public_room_id'])) {
			
			$cCategId = $arr['room_id'];
			$cTypeId = $arr['type'];			
			$cPublicId = $arr['public_room_id'];
		
			$sql = "
				select upper(room.ITEM_PUBLIC_ROOM_NAME) ITEM_PUBLIC_ROOM_NAME, 
				categ.ITEM_PUBLIC_ROOM_QTY, categ.ITEM_PUBLIC_ROOM_UOM 
				from FM_MST_ITEM_PUBLIC_ROOM room
				inner join FM_MST_ITEM_CATEGORY categ 
				ON categ.ITEM_PUBLIC_ROOM_ID = room.ITEM_PUBLIC_ROOM_ID
				Where
					categ.MST_CATEGORY_ID = $cCategId 
				And categ.MST_TYPE_ID = $cTypeId 
				And room.ITEM_PUBLIC_ROOM_ID = '$cPublicId'
			";
			
			$query = $this->db->query($sql);
            return $query->row_array();
		}
	}
	
	function save_detail_room($arr) {
		if (!empty($arr['room_id']) && !empty($arr['type']) && !empty($arr['public_room_id'])) {
			
			$cCategId = $arr['room_id'];
			$cTypeId = $arr['type'];
			$cPublicId = $arr['public_room_id'];
			
			$sql = "
				INSERT INTO FM_MST_ITEM_CATEGORY 
				(MST_CATEGORY_ID,ITEM_PUBLIC_ROOM_ID,ITEM_PUBLIC_ROOM_QTY,ITEM_PUBLIC_ROOM_UOM,MST_TYPE_ID) 
				VALUES 
				($cCategId,'$cPublicId',1,'Each',$cTypeId) 				
			";
			
			$query = $this->db->query($sql);
            return true;
		}
	}
	
	function save_detail_room2($arr) {
		if (!empty($arr['room_id']) && !empty($arr['type']) && !empty($arr['public_room_id'])) {
			
			$cCategId = $arr['room_id'];
			$cTypeId = $arr['type'];
			$cQty = $arr['qty'];
			$cUom = $arr['uom'];
			$cPublicId = $arr['public_room_id'];
			
			$sql = "
				UPDATE FM_MST_ITEM_CATEGORY SET 
				ITEM_PUBLIC_ROOM_QTY = $cQty, 
				ITEM_PUBLIC_ROOM_UOM = '$cUom' 
				WHERE 
				MST_CATEGORY_ID = $cCategId	
				And MST_TYPE_ID = $cTypeId   
				And ITEM_PUBLIC_ROOM_ID = '$cPublicId' 
			";
			
			$query = $this->db->query($sql);
            return true;
		}
	}
	
	function header_data($arr) {
		$sql = "
			select a.MST_TYPE_DESC, b.MST_CATEGORY_DESC, c.MST_FLOOR_DESC 
			from fm_mst_type a, fm_mst_category b, fm_mst_floor c 
				where a.mst_type_id         = b.mst_type_id and 
					  a.mst_type_id         = c.mst_type_id and 
					  b.mst_floor_id        = c.mst_floor_id and 
					  a.mst_type_status     = 'Y' and 
					  c.mst_floor_status    = 'Y' and 
					  b.mst_category_id = '" . $arr['room_id']."' and 
					  b.mst_type_id = '" . $arr['type']."' 
		";
		
		return $this->db->query($sql)->row_array();        
	}
	
	function room_list($arr) {
        if (!empty($arr['room_id'])) {
        
			$sql = "
				select a.mst_type_id, a.mst_type_desc, b.mst_category_id, b.mst_category_desc, 
				c.mst_floor_id, c.mst_floor_desc, b.mst_category_status, b.mst_category_capacity        
				from fm_mst_type a, fm_mst_category b, fm_mst_floor c 
				where a.mst_type_id         = b.mst_type_id and 
					  a.mst_type_id         = c.mst_type_id and 
					  b.mst_floor_id        = c.mst_floor_id and 
					  a.mst_type_status     = 'Y' and 
					  c.mst_floor_status    = 'Y' and b.mst_category_id = '" . $arr['room_id']."'  								
			";					

			$query = $this->db->query($sql);
            return $query->row_array();
        }
    }
	
	function search_room($arr, $limit = NULL) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_room'])) {
                $whereCond .= " and upper(mst_category_desc) like upper('%" . $arr['search_by_room'] . "%')";
            }
            if (!empty($arr['search_by_floor'])) {
                $whereCond .= " and upper(mst_floor_desc) = upper('" . $arr['search_by_floor'] . "')";
            }
            if (!empty($arr['search_by_type'])) {
                $whereCond .= " and a.mst_type_id = '" . $arr['search_by_type'] . "' ";
            }
        }

        $sql = "
			select mst_type_id, mst_type_desc, mst_category_id, mst_category_desc, 
			mst_floor_id, mst_floor_desc, mst_category_status, recnum from ( 
			
			select mst_type_id, mst_type_desc, mst_category_id, mst_category_desc, 
			mst_floor_id, mst_floor_desc, mst_category_status, rownum recnum from ( 

			select a.mst_type_id, a.mst_type_desc, b.mst_category_id, b.mst_category_desc, 
			c.mst_floor_id, c.mst_floor_desc, b.mst_category_status     
			from fm_mst_type a, fm_mst_category b, fm_mst_floor c 
			where a.mst_type_id         = b.mst_type_id and 
				  a.mst_type_id         = c.mst_type_id and 
				  b.mst_floor_id        = c.mst_floor_id and 
				  a.mst_type_status     = 'Y' and 
				  c.mst_floor_status    = 'Y' $whereCond
			order by a.mst_type_desc asc, b.mst_category_desc asc, c.mst_floor_desc asc )) 
			
			$limit		
		";
        
		$query = $this->db->query($sql);
        return $query->result_array();
    }
	
	function update_status_room($arr) {
        $room_id = $arr['room_id'];
        $cond = $arr['cond'];
        
        if (!empty($room_id)) {
            $sql_update = " 
				update fm_mst_category set mst_category_status = '$cond' 
                where mst_category_id = $room_id 
			";
			
            $this->db->query($sql_update);
        }
    }
	
	function update_room($arr) {
        $arr['room_id'] = trim($this->input->post('room_id'));
		$arr['room'] = trim($this->input->post('room'));
		$arr['type'] = trim($this->input->post('type'));
		$arr['floor'] = trim($this->input->post('floor'));
		$arr['status'] = trim($this->input->post('status'));
		$arr['capacity'] = trim($this->input->post('capacity'));
	
		$category_id 	   = $arr['room_id'];
        $category_desc 	   = $arr['room'];
		$type_id		   = $arr['type'];
		$floor_desc 	   = $arr['floor'];
		$category_status   = $arr['status'];
		$category_capacity = $arr['capacity'];		
		
		$getseq = " select MST_FLOOR_ID ".
			 " from FM_MST_FLOOR where upper(MST_FLOOR_DESC) = upper('$floor_desc') and ROWNUM=1 order by MST_FLOOR_ID DESC ";
		$vFloor = $this->db->query($getseq)->row_array();
		$vFloorId = $vFloor['MST_FLOOR_ID'];
		
		$vTypeId = $type_id;	
		$user = 999999; //$this->session->userdata('ID');		
		if ($category_capacity==""){$category_capacity="12";}
		
		if (!empty($category_id)) {
			$sql_update = "
				update fm_mst_category set 
				mst_type_id = $vTypeId, 
				mst_category_desc = '$category_desc',  
				mst_category_status = '$category_status', 
				mst_floor_id = $vFloorId, 
				mst_category_capacity = '$category_capacity', 
				USRUPD = $user,  
                DTMUPD = SYSDATE  
				where mst_category_id = $category_id 								
			";
			
			$this->db->query($sql_update);
		}		        
    }
	
	function insert_room_item($arr) {
		$arr['item'] 		= trim($this->input->post('item'));
		$arr['quantity'] 	= trim($this->input->post('quantity'));
		$arr['uom'] 		= trim($this->input->post('uom'));
		$arr['status'] 		= trim($this->input->post('status'));
		
		$PR_NAME = $arr['item'];
		$PR_QTY = $arr['quantity'];
		$PR_UOM = $arr['uom'];
		$PR_STATUS = $arr['status'];
		
		$user = 999999; //$this->session->userdata('ID');
		if ($PR_QTY == ""){$PR_QTY ="1";}
		
		$sql_item = " 
			select max(to_number(substr(ITEM_PUBLIC_ROOM_ID,1,5),'99999')) ITEM_PUBLIC_ROOM_ID_OLD,  			
			rtrim(to_char(sysdate,'RM')) romawi from FM_MST_ITEM_PUBLIC_ROOM 
		";
		$a = $this->db->query($sql_item)->row_array();
		
		$cRoomItemIdOld 	= $a["ITEM_PUBLIC_ROOM_ID_OLD"];	
		$cRomawi        	= $a["ROMAWI"];	
		$cRoomItemIdNew 	= $cRoomItemIdOld +1;

		$sql_item2 = "
			select lpad($cRoomItemIdNew,5,0)||'/RIM/$cRomawi/'||TO_CHAR(SYSDATE,'YYYY') room_item_id from dual 
		"; 
		$b = $this->db->query($sql_item2)->row_array();
		$PR_ID = $b["ROOM_ITEM_ID"];	
					
		$sql_insert_room_item = "
			insert into FM_MST_ITEM_PUBLIC_ROOM (ITEM_PUBLIC_ROOM_ID,ITEM_PUBLIC_ROOM_NAME,ITEM_PUBLIC_ROOM_STATUS,ITEM_PUBLIC_ROOM_QTY,ITEM_PUBLIC_ROOM_UOM,USRUPD,DTMUPD) 
			values ('$PR_ID', upper('$PR_NAME'), '$PR_STATUS', $PR_QTY, '$PR_UOM', $user, SYSDATE) 
		";
		
		$this->db->query($sql_insert_room_item);
	}
	
	function insert_room($arr) {		     
		$arr['room'] = trim($this->input->post('room'));
		$arr['type'] = trim($this->input->post('type'));
		$arr['floor'] = trim($this->input->post('floor'));
		$arr['status'] = trim($this->input->post('status'));
		$arr['capacity'] = trim($this->input->post('capacity'));

		$category_desc 	   = $arr['room'];
		$type_id		   = $arr['type'];
		$floor_desc 	   = $arr['floor'];
		$category_status   = $arr['status'];
		$category_capacity = $arr['capacity'];
		
		$user = 999999; //$this->session->userdata('ID');		
		$arr['p_type'] = trim($this->input->post('p_type'));
		$arr['p_floor'] = trim($this->input->post('p_floor'));
		
		$pTypeDesc = $arr['p_type'];
		$pFloorDesc = $arr['p_floor'];
		
		//Insert into fm_mst_type & fm_mst_floor
		if(!empty($floor_desc) && $floor_desc == "Others" && !empty($type_id) && $type_id == "Others") {		
		
			$stmt =	" insert into fm_mst_type (mst_type_desc, mst_type_status, USRUPD, DTMUPD) ".
					" values ('$pTypeDesc', 'Y', $user, SYSDATE)";
			$this->db->query($stmt);
			
			$getdet = " select MST_TYPE_ID ".
					" from fm_mst_type where upper(MST_TYPE_DESC) like upper('%$pTypeDesc%')";
			$vType = $this->db->query($getdet)->row_array();
			$vTypeId = $vType['MST_TYPE_ID'];
			
			$stmt2 = " insert into fm_mst_floor (mst_type_id, mst_floor_desc, mst_floor_status, USRUPD, DTMUPD ) ".
					" values ('$vTypeId', '$pFloorDesc', 'Y', $user, SYSDATE)";
			$this->db->query($stmt2);
			
			$getdet2 = " select MST_FLOOR_ID ".
                 " from FM_MST_FLOOR where upper(MST_FLOOR_DESC) = upper('$pFloorDesc') and ROWNUM=1 
				   order by MST_FLOOR_ID DESC ";
			$vFloor = $this->db->query($getdet2)->row_array();
			$vFloorId = $vFloor['MST_FLOOR_ID'];
					
		}else if(!empty($floor_desc) && $floor_desc == "Others" && !empty($type_id) && $type_id != "Others") {
		
			$stmt2 = " insert into fm_mst_floor (mst_type_id, mst_floor_desc, mst_floor_status, USRUPD, DTMUPD ) ".
					" values ('$type_id', '$pFloorDesc', 'Y', $user, SYSDATE)";
			$this->db->query($stmt2);
			
			$getdet2 = " select MST_FLOOR_ID ".
                 " from FM_MST_FLOOR where upper(MST_FLOOR_DESC) = upper('$pFloorDesc') and ROWNUM=1 
				   order by MST_FLOOR_ID DESC ";
			$vFloor = $this->db->query($getdet2)->row_array();
			$vFloorId = $vFloor['MST_FLOOR_ID'];
			
			$vTypeId = $type_id;
		
		}else if(!empty($floor_desc) && $floor_desc != "Others" && !empty($type_id) && $type_id == "Others") {
		
			$stmt =	" insert into fm_mst_type (mst_type_desc, mst_type_status, USRUPD, DTMUPD ) ".
					" values ('$pTypeDesc', 'Y', $user, SYSDATE)";
			$this->db->query($stmt);
			
			$getdet = " select MST_TYPE_ID ".
					" from fm_mst_type where upper(MST_TYPE_DESC) like upper('%$pTypeDesc%')";
			$vType = $this->db->query($getdet)->row_array();
			$vTypeId = $vType['MST_TYPE_ID'];
			
			$stmt2 = " insert into fm_mst_floor (mst_type_id, mst_floor_desc, mst_floor_status, USRUPD, DTMUPD ) ".
					" values ('$vTypeId', '$floor_desc', 'Y', $user, SYSDATE)";
			$this->db->query($stmt2);						
			
			$getdet2 = " select MST_FLOOR_ID ".
                 " from FM_MST_FLOOR where upper(MST_FLOOR_DESC) = upper('$floor_desc') and ROWNUM=1 
				   order by MST_FLOOR_ID DESC ";
			$vFloor = $this->db->query($getdet2)->row_array();
			$vFloorId = $vFloor['MST_FLOOR_ID'];
			
		} else {
		
			$stmt2 = "insert into fm_mst_floor (mst_type_id, mst_floor_desc, mst_floor_status, USRUPD, DTMUPD ) ".
					" values ('$type_id', '$floor_desc', 'Y', $user, SYSDATE)";
			$this->db->query($stmt2);
			
			$getdet2 = " select MST_FLOOR_ID ".
                 " from FM_MST_FLOOR where upper(MST_FLOOR_DESC) = upper('$floor_desc') and ROWNUM=1 
				   order by MST_FLOOR_ID DESC ";
			$vFloor = $this->db->query($getdet2)->row_array();
			$vFloorId = $vFloor['MST_FLOOR_ID'];
			
			$vTypeId = $type_id;				
		}
		
		if ($category_capacity==""){$category_capacity="12";}
		
		$sql_insert_room = "
			insert into fm_mst_category (mst_type_id,mst_category_desc,mst_category_status,mst_floor_id,mst_category_capacity,USRUPD,DTMUPD) 
			values ($vTypeId, '$category_desc', '$category_status', '$vFloorId', '$category_capacity', $user, SYSDATE) 
		";
		
		$this->db->query($sql_insert_room);
				
    }

    //=======================================================================================================================
    // AREA MODEL, By : Hasyim A
    //=======================================================================================================================

    function count_area($arr) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_regional'])) {
                $whereCond .= " and upper(regional_name) like upper('%" . $arr['search_by_regional'] . "%')";
            }
            if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(area_status) =upper('" . $arr['search_by_status'] . "')";
            }
            if (!empty($arr['search_by_type'])) {
                $whereCond .= " and upper(isdomestic) =upper('" . $arr['search_by_type'] . "')";
            }
        }

        $sql = " SELECT COUNT (*) cnt
                    FROM (SELECT area_status, area_desc, regional_address, area_id, area_name,
                                 regional_name, isdomestic,  ismessenger
                            FROM (SELECT   area_status, area_desc, regional_address, area_id,
                                           area_name, regional_name, isdomestic, ismessenger
                                      FROM fm_mst_area 
                                         where 1=1 $whereCond
                                  ORDER BY area_id DESC))";
        $result = $this->db->query($sql)->row();
        return $result->CNT;
    }

    function list_area($arr, $limit = NULL) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_regional'])) {
                $whereCond .= " and upper(regional_name) like upper('%" . $arr['search_by_regional'] . "%')";
            }
            if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(area_status) =upper('" . $arr['search_by_status'] . "')";
            }
            if (!empty($arr['search_by_type'])) {
                $whereCond .= " and upper(isdomestic) =upper('" . $arr['search_by_type'] . "')";
            }
        }

		$sql_old = "
			SELECT 
			recnum, area_id, concat(concat(area_name,' - '),area_desc) regional_name, regional_name,  
			case when isdomestic = 'Y' then 'Domestik' else 'Internasional' end as isdomestic,  
			case when area_status = 'Y' then 'Active' else 'Not Active' end as area_status, 
			regional_address 
			FROM (SELECT area_status, area_desc, regional_address, area_id, area_name,
			regional_name, isdomestic, ROWNUM recnum
			FROM (SELECT   area_status, area_desc, regional_address, area_id,
			area_name, regional_name, isdomestic
			FROM fm_mst_area ORDER BY area_id DESC))
		";

        $sql = " 
			SELECT area_status, area_desc, regional_address, area_id, area_name,
			regional_name, isdomestic, ismessenger, recnum
			FROM (SELECT area_status, area_desc, regional_address, area_id, area_name,
			regional_name, isdomestic, ROWNUM recnum, ismessenger
			FROM (SELECT   area_status, area_desc, regional_address, area_id,
			area_name, regional_name, isdomestic, ismessenger
			FROM fm_mst_area
			where 1=1 $whereCond
            ORDER BY area_id DESC)) $limit 
		";

		$query = $this->db->query($sql);
        return $query->result_array();
    }

    function search_area($arr, $limit = NULL) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_regional'])) {
                $whereCond .= " and upper(regional_name) like upper('%" . $arr['search_by_regional'] . "%')";
            }
            if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(area_status) =upper('" . $arr['search_by_status'] . "')";
            }
            if (!empty($arr['search_by_type'])) {
                $whereCond .= " and upper(isdomestic) =upper('" . $arr['search_by_type'] . "')";
            }
        }

        $sql = " SELECT area_status, area_desc, regional_address, area_id, area_name,
                    regional_name, isdomestic, ismessenger, recnum
               FROM (SELECT area_status, area_desc, regional_address, area_id, area_name,
                            regional_name, isdomestic, ROWNUM recnum, ismessenger
                       FROM (SELECT   area_status, area_desc, regional_address, area_id,
                                      area_name, regional_name, isdomestic, ismessenger
                                 FROM fm_mst_area
                                    where 1=1 $whereCond
                             ORDER BY area_id DESC)) $limit";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function detil_area($arr) {
        if (!empty($arr['id'])) {
            $sql = "SELECT *
                    FROM fm_mst_area
                    WHERE area_id=" . $arr['id'];
            $query = $this->db->query($sql);
            return $query->row_array();
        }
    }

    function insert_area($arr) {
        $arr['area'] = trim($this->input->post('area'));
        $arr['regional'] = trim($this->input->post('regional'));
        $arr['officename'] = trim($this->input->post('officename'));
        $arr['address'] = trim($this->input->post('address'));
        $arr['status'] = trim($this->input->post('status'));
        $arr['type'] = trim($this->input->post('type'));

        $area = $arr['area'];
        $regional = $arr['regional'];
        $officename = $arr['officename'];
        $address = $arr['address'];
        $status = strtoupper($arr['status']);
        $type = strtoupper($arr['type']);
        $login = $this->session->userdata('login');
        $user = $login['HR_ID'];

        $sql_insert = "insert into fm_mst_area(area_name, area_desc, regional_name, regional_address, area_status, ISDOMESTIC, USRUPD, DTMUPD) " .
                "values ('$area','$regional','$officename','$address','$status', '$type', $user, SYSDATE)";
        $this->db->query($sql_insert);
    }

    function update_area($arr) {
        $arr['idarea'] = trim($this->input->post('idarea'));
        $arr['area'] = trim($this->input->post('area'));
        $arr['regional'] = trim($this->input->post('regional'));
        $arr['officename'] = trim($this->input->post('officename'));
        $arr['address'] = trim($this->input->post('address'));
        $arr['status'] = trim($this->input->post('status'));
        $arr['type'] = trim($this->input->post('type'));

        $area_id = $arr['idarea'];
        $area = $arr['area'];
        $regional = $arr['regional'];
        $officename = $arr['officename'];
        $address = $arr['address'];
        $status = strtoupper($arr['status']);
        $type = strtoupper($arr['type']);
        $login = $this->session->userdata('login');
        $user = $login['HR_ID'];

        if (!empty($area_id)) {
            $sql_update = "update fm_mst_area set area_name		='$area', " .
                    "area_desc		='$regional', " .
                    "regional_name	='$officename', " .
                    "regional_address   ='$address', " .
                    "area_status	='$status', " .
                    "ISDOMESTIC         ='$type', " .
                    "USRUPD             = $user, " .
                    "DTMUPD             =SYSDATE " .
                    "where area_id	=$area_id";
            $this->db->query($sql_update);
        }
    }

    function update_status_area($arr) {
        $id = $arr['id'];
        $cond = $arr['cond'];

        if (!empty($id)) {
            $sql_update = "update fm_mst_area set area_status ='$cond' " .
                    "where area_id=$id";
            $this->db->query($sql_update);
        }
    }

    //=======================================================================================================================
    // END OF AREA MODEL
    //=======================================================================================================================
    //=======================================================================================================================
    // COURIER MODEL, By : Hasyim A
    //=======================================================================================================================

    function count_courier($arr) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_courier'])) {
                $whereCond .= " and upper(a.courier_name) like upper('%" . $arr['search_by_courier'] . "%')";
            }
            if (!empty($arr['search_by_area'])) {
                $whereCond .= " and upper(c.area_name) like upper('%" . $arr['search_by_area'] . "%')";
            }
            if (!empty($arr['search_by_regional'])) {
                $whereCond .= " and upper(c.regional_name) like upper('%" . $arr['search_by_regional'] . "%')";
            }
            if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(b.price_status) =upper('" . $arr['search_by_status'] . "')";
            }
        }

        $sql = " SELECT COUNT (*) cnt
                    FROM (SELECT price_id
                            FROM (SELECT   b.price_id
                                      FROM fm_mst_courier a, fm_courier_price b, fm_mst_area c
                                     WHERE a.courier_id = b.courier_id AND c.area_id = b.area_id $whereCond
                                  ORDER BY a.courier_name ASC))";
        $result = $this->db->query($sql)->row();
        return $result->CNT;
    }

    function list_courier($arr, $limit = NULL) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_courier'])) {
                $whereCond .= " and upper(a.courier_name) like upper('%" . $arr['search_by_courier'] . "%')";
            }
            if (!empty($arr['search_by_area'])) {
                $whereCond .= " and upper(c.area_name) like upper('%" . $arr['search_by_area'] . "%')";
            }
            if (!empty($arr['search_by_regional'])) {
                $whereCond .= " and upper(c.regional_name) like upper('%" . $arr['search_by_regional'] . "%')";
            }
            if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(b.price_status) =upper('" . $arr['search_by_status'] . "')";
            }
        }

        $sql = " SELECT RECNUM, price_id, price_status, courier_id, courier_name, price_1kg,
                    price_15kg, price_5kg, area_name, regional_name
               FROM (SELECT price_id, price_status, courier_id, courier_name, price_1kg,
                            price_15kg, price_5kg, area_name, regional_name, ROWNUM RECNUM
                       FROM (SELECT   b.price_id, b.price_status, a.courier_id,
                                      a.courier_name, b.price_1kg, b.price_15kg,
                                      b.price_5kg, c.area_name, c.regional_name
                                 FROM fm_mst_courier a, fm_courier_price b, fm_mst_area c
                                WHERE a.courier_id = b.courier_id AND c.area_id = b.area_id $whereCond
                             ORDER BY a.courier_name ASC)) $limit";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function search_courier($arr, $limit = NULL) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_courier'])) {
                $whereCond .= " and upper(a.courier_name) like upper('%" . $arr['search_by_courier'] . "%')";
            }
            if (!empty($arr['search_by_area'])) {
                $whereCond .= " and upper(c.area_name) like upper('%" . $arr['search_by_area'] . "%')";
            }
            if (!empty($arr['search_by_regional'])) {
                $whereCond .= " and upper(c.regional_name) like upper('%" . $arr['search_by_regional'] . "%')";
            }
            if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(b.price_status) =upper('" . $arr['search_by_status'] . "')";
            }
        }

        $sql = " SELECT RECNUM, price_id, price_status, courier_id, courier_name, price_1kg,
                    price_15kg, price_5kg, area_name, regional_name
               FROM (SELECT price_id, price_status, courier_id, courier_name, price_1kg,
                            price_15kg, price_5kg, area_name, regional_name, ROWNUM RECNUM
                       FROM (SELECT   b.price_id, b.price_status, a.courier_id,
                                      a.courier_name, b.price_1kg, b.price_15kg,
                                      b.price_5kg, c.area_name, c.regional_name
                                 FROM fm_mst_courier a, fm_courier_price b, fm_mst_area c
                                WHERE a.courier_id = b.courier_id AND c.area_id = b.area_id $whereCond
                             ORDER BY a.courier_name ASC)) $limit";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function detil_courier($arr) {
        if (!empty($arr['price_id'])) {
            $sql = "SELECT c.area_id, b.price_status, a.courier_id, a.courier_name, b.price_id, b.price_1kg,
                        b.price_15kg, b.price_5kg, c.area_name, c.regional_name, 
                        CASE
                           WHEN c.isdomestic = 'Y'
                              THEN 'DOMESTIC'
                           ELSE 'INTERNATIONAL'
                        END isdomestic
                   FROM fm_mst_courier a, fm_courier_price b, fm_mst_area c
                  WHERE a.courier_id = b.courier_id AND c.area_id = b.area_id
                        AND b.price_id =" . $arr['price_id'];
            $query = $this->db->query($sql);
            return $query->row_array();
        }
    }

    function get_new_courier_id() {
        $sql1 = "SELECT MAX (TO_NUMBER (SUBSTR (courier_id, 4, 4), '99999')) courier_id_max
                    FROM fm_mst_courier
                   WHERE courier_id != 'MESSENGER'";
        $query1 = $this->db->query($sql1)->row_array();
        $courier_max_id = intval($query1['COURIER_ID_MAX']) + 1;

        $sql2 = "select 'COU'||lpad($courier_max_id,4,0) COURIER_ID  from dual";
        $query2 = $this->db->query($sql2)->row_array();
        return $query2['COURIER_ID'];
    }

    function insert_courier($arr) {
        $id = $this->get_new_courier_id();
        $couriernew = $arr['couriernew'];
        $couriername = $arr['couriername'];
        $area_id = $arr['areaname'];
        $status = $arr['status'];
        $price1kg = $arr['price1kg'];
        $price15kg = $arr['price15kg'];
        $price5kg = $arr['price5kg'];
        $login = $this->session->userdata('login');
        $user = $login['HR_ID'];

        if ($couriername == 'other' && $couriernew != '') {
            $sql_insert_mst = "insert into fm_mst_courier(courier_id, courier_name, courier_status, usrupd, dtmupd) " .
                    " values ('$id', '$couriernew', 'Y', $user, sysdate)";
            $this->db->query($sql_insert_mst);
            $couriername = $id;
        }

        $sql_insert_price = "insert into fm_courier_price(price_status, price_1kg, price_15kg, price_5kg, courier_id, area_id) " .
                " values ('$status', '$price1kg', '$price15kg', '$price5kg', '$couriername', $area_id)";
        $this->db->query($sql_insert_price);

//        if ($couriername != "MESSENGER") {
//            $sql_insert_courier_mapping = "insert into FM_MST_COURIER_MAPPING(COURIER_ID, ID_COURIER_TYPE) values ('$couriername', '$services')";
//            $this->db->query($sql_insert_courier_mapping);
//        } else {
//            $sql_insert_mapping = "insert into FM_MST_COURIER_MAPPING(COURIER_ID, ID_COURIER_TYPE) values ('$couriername', 'MES')";
//            $this->db->query($sql_insert_mapping);
//            $sql_update_area = "update fm_mst_area set ismessenger = 'Y' where area_id = $area_id";
//            $this->db->query($sql_update_area);
//        }
        if ($couriername == "MESSENGER") {
            $sql_update_area = "update fm_mst_area set ismessenger = 'Y' where area_id = $area_id";
            $this->db->query($sql_update_area);
        }
    }

    function update_courier($arr) {
        $status = $arr['status'];
        $price1kg = $arr['price1kg'];
        $price15kg = $arr['price15kg'];
        $price5kg = $arr['price5kg'];
        $price_id = $arr['price_id'];

        $sql_update_price = "update fm_courier_price set " .
                "price_1kg		= '$price1kg', " .
                "price_15kg		= '$price15kg'," .
                "price_5kg		= '$price5kg', " .
                "price_status	= '$status' " .
                "where price_id = $price_id ";
        $this->db->query($sql_update_price);
    }

    function update_status_courier($arr) {
        $id = $arr['id'];
        $cond = $arr['cond'];

        if (!empty($id)) {
            $sql_update = "update fm_courier_price set PRICE_STATUS ='$cond' " .
                    "where PRICE_ID=$id";
            $this->db->query($sql_update);
        }
    }

    function courier_area_list() {
        $sql = "SELECT DISTINCT area_name
                FROM fm_mst_area order by 1";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function courier_regional_list($area) {
        $sql = "SELECT DISTINCT regional_name
                FROM fm_mst_area
               WHERE TRIM (UPPER (area_name)) = upper('$area')
            ORDER BY 1";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function courier_list_add() {
        $sql = "SELECT   courier_id, courier_name
                    FROM fm_mst_courier
                   WHERE courier_status = 'Y'
                ORDER BY courier_name ASC";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function courier_area_list_add() {
        $sql = "SELECT   area_id, area_name, regional_name
                FROM fm_mst_area
               WHERE area_status = 'Y'
            ORDER BY area_name ASC, regional_name ASC";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function check_area($arr) {
        if (isset($arr['area']) && !empty($arr['area'])) {
            $sql = "SELECT ISDOMESTIC from fm_mst_area where area_id=" . intval($arr['area']);
            $query = $this->db->query($sql);
            return $query->row_array();
        }
    }

    //=======================================================================================================================
    // END OF COURIER MODEL
    //=======================================================================================================================
    //=======================================================================================================================
    // MESSENGER MODEL, By : Hasyim A
    //=======================================================================================================================

    function count_messenger($arr) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_messenger'])) {
                $whereCond .= " and messenger_id=" . $arr['search_by_messenger'];
            }
            if (!empty($arr['search_by_date'])) {
                $whereCond .= " and to_char(trans_date, 'dd-mm-yyyy') = '" . $arr['search_by_date'] . "'";
            }
            if (!empty($arr['search_by_approval'])) {
                if(strtoupper($arr['search_by_approval'])=='Y'){
                    $whereCond .= " and upper(trans_app) = 'Y'";
                }else{
                    $whereCond .= " and (upper(trans_app) = 'N' or trans_app is null)";
                }
            }
            if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(trans_status) = upper('" . $arr['search_by_status'] . "')";
            }
        }

        $sql = " SELECT COUNT (*) cnt
                    FROM (SELECT   messenger_id
                              FROM fm_mst_messenger mst INNER JOIN fm_trans_messenger trans
                                   ON mst.messenger_id = trans.trans_messenger_id
                             WHERE TRUNC (trans.trans_date) >= TRUNC (SYSDATE) $whereCond) ";
        $result = $this->db->query($sql)->row();
        return $result->CNT;
    }

    function list_messenger($arr, $limit = NULL) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_messenger'])) {
                $whereCond .= " and messenger_id=" . $arr['search_by_messenger'];
            }
            if (!empty($arr['search_by_date'])) {
                $whereCond .= " and to_char(trans_date, 'dd-mm-yyyy') = '" . $arr['search_by_date'] . "'";
            }
            if (!empty($arr['search_by_approval'])) {
                if(strtoupper($arr['search_by_approval'])=='Y'){
                    $whereCond .= " and upper(trans_app) = 'Y'";
                }else{
                    $whereCond .= " and (upper(trans_app) = 'N' or trans_app is null)";
                }
            }
            if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(trans_status) = upper('" . $arr['search_by_status'] . "')";
            }
        }

        $sql = " SELECT RECNUM, messenger_id, trans_date, trans_app, trans_status, messenger_name
                    FROM (SELECT messenger_id, trans_date, trans_app, messenger_name,
                                 trans_status, ROWNUM RECNUM
                            FROM (SELECT   messenger_name,
                                           TO_CHAR (trans_date, 'dd/mm/yyyy') trans_date,
                                           trans_app, trans_status, messenger_id
                                      FROM fm_mst_messenger mst INNER JOIN fm_trans_messenger trans
                                           ON mst.messenger_id = trans.trans_messenger_id
                                     WHERE TRUNC (trans.trans_date) >= TRUNC (SYSDATE) $whereCond
                                  ORDER BY trans_date ASC)) $limit";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function search_messenger($arr, $limit = NULL) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_messenger'])) {
                $whereCond .= " and messenger_id=" . $arr['search_by_messenger'];
            }
            if (!empty($arr['search_by_date'])) {
                $whereCond .= " and to_char(trans_date, 'dd-mm-yyyy') = '" . $arr['search_by_date'] . "'";
            }
            if (!empty($arr['search_by_approval'])) {
                if(strtoupper($arr['search_by_approval'])=='Y'){
                    $whereCond .= " and upper(trans_app) = 'Y'";
                }else{
                    $whereCond .= " and (upper(trans_app) = 'N' or trans_app is null)";
                }
            }
            if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(trans_status) = upper('" . $arr['search_by_status'] . "')";
            }
        }

        $sql = " 
			SELECT RECNUM, messenger_id, trans_date, trans_app, trans_status, messenger_name
			FROM (SELECT messenger_id, trans_date, trans_app, messenger_name,
			trans_status, ROWNUM RECNUM
			FROM (SELECT   messenger_name,
			TO_CHAR (trans_date, 'dd/mm/yyyy') trans_date,
			trans_app, trans_status, messenger_id
			FROM fm_mst_messenger mst INNER JOIN fm_trans_messenger trans
			ON mst.messenger_id = trans.trans_messenger_id
			WHERE TRUNC (trans.trans_date) >= TRUNC (SYSDATE) $whereCond
			ORDER BY trans_date ASC)) $limit
		";
								  
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function get_new_messenger_id($id, $date1, $date2) {
        $sql1 = "select abs(to_date('$date2','dd/mm/yyyy') - to_date('$date1','dd/mm/yyyy')) jmlh_hari from dual";
        $query1 = $this->db->query($sql1)->row_array();
        $jumlahhari_new = 0;
        if (!empty($query1)) {
            $jumlahhari = $query1["JMLH_HARI"];
            $jumlahhari_new = $jumlahhari + 1;
        }

        $sql2 = "select MAX(TRANS_ID) TRANS_ID from fm_trans_messenger where TRANS_MESSENGER_ID = '$id'";
        $query2 = $this->db->query($sql2)->row_array();
        $trans_id = $query2['TRANS_ID'];
        
        $data['jumlah_hari'] = $jumlahhari_new;
        $data['trans_id'] = $trans_id;

        return $data;
    }

    function insert_messenger($arr) {
        $date1 = $arr['date1'];
        $date2 = $arr['date2'];
        $messengername = $arr['messengername'];
        $assignto = $arr['assignto'];
        $description = $arr['description'];

        $data = $this->get_new_messenger_id($messengername, $date1, $date2);

        $jumlah_hari = $data['jumlah_hari'];
        $trans_id = $data['trans_id'];

        if ($jumlah_hari > 0) {
            for ($r = 0; $r < $jumlah_hari; $r++) {
                $sql1 = "select to_char(to_date('$date1','dd/mm/yyyy')+$r,'dd/mm/yyyy') trans_tgl_awal_new from dual";
                $query1 = $this->db->query($sql1)->row_array();
                $tanggal = $query1['TRANS_TGL_AWAL_NEW'];
                
                $sql2 = "insert into fm_trans_messenger (trans_id, trans_messenger_id, trans_id_hist, trans_date, TRANS_STATUS, TRANS_DESC, trans_app) values 
                               (SEQ_TRANS_MESSENGER.nextval, '$messengername', '$trans_id', to_date('$tanggal','dd/mm/yyyy'), '$assignto', '$description', 'N')";
                $this->db->query($sql2);
            }
        }
    }

    function insert_messenger_list($arr) {
        $messengername = $arr['messengername'];
        $messengerphone = $arr['messengerphone'];
        $messengerstatus = $arr['messengerstatus'];
        $login = $this->session->userdata('login');
        $user = $login['HR_ID'];

        $sql = "insert into fm_mst_messenger(messenger_id, messenger_name, messenger_phone, messenger_status, create_by, create_date) 
				values (SEQ_MESSENGER.nextval, '$messengername', '$messengerphone', '$messengerstatus', '$user', sysdate)";
        $this->db->query($sql);
    }

    function messenger_list() {
        $sql = "SELECT messenger_id, messenger_name
                FROM fm_mst_messenger
               WHERE messenger_status = 'Y'";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    //=======================================================================================================================
    // END OF MESSENGER MODEL
    //=======================================================================================================================
    //=======================================================================================================================
    // APPROVAL MESSENGER MODEL, By : Hasyim A
    //=======================================================================================================================

    function count_approval_messenger($arr) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_messenger'])) {
                $whereCond .= " and messenger_id=" . $arr['search_by_messenger'];
            }
            if (!empty($arr['search_by_date'])) {
                $whereCond .= " and to_char(trans_date, 'dd-mm-yyyy') = '" . $arr['search_by_date'] . "'";
            }
            if (!empty($arr['search_by_approval'])) {
                if(strtoupper($arr['search_by_approval'])=='Y'){
                    $whereCond .= " and upper(trans_app) = 'Y'";
                }else{
                    $whereCond .= " and (upper(trans_app) = 'N' or trans_app is null)";
                }
            }
            if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(trans_status) = upper('" . $arr['search_by_status'] . "')";
            }
        }

        $sql = " SELECT COUNT (*) cnt
                    FROM (SELECT RECNUM, trans_id, messenger_id, trans_date, trans_app, trans_status,
                        messenger_name
                   FROM (SELECT trans_id, messenger_id, trans_date, trans_app, messenger_name,
                                trans_status, ROWNUM RECNUM
                           FROM (SELECT   trans_id, messenger_name,
                                          TO_CHAR (trans_date, 'dd/mm/yyyy') trans_date,
                                          trans_app, trans_status, messenger_id
                                     FROM fm_mst_messenger mst INNER JOIN fm_trans_messenger trans
                                          ON mst.messenger_id = trans.trans_messenger_id
                                    WHERE TRUNC (trans.trans_date) >= TRUNC (SYSDATE)
                                      AND trans.trans_app IS NULL
                                      $whereCond
                                ORDER BY messenger_id ASC)) ) ";
        $result = $this->db->query($sql)->row();
        return $result->CNT;
    }

    function list_approval_messenger($arr, $limit = NULL) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_messenger'])) {
                $whereCond .= " and messenger_id=" . $arr['search_by_messenger'];
            }
            if (!empty($arr['search_by_date'])) {
                $whereCond .= " and to_char(trans_date, 'dd-mm-yyyy') = '" . $arr['search_by_date'] . "'";
            }
            if (!empty($arr['search_by_approval'])) {
                if(strtoupper($arr['search_by_approval'])=='Y'){
                    $whereCond .= " and upper(trans_app) = 'Y'";
                }else{
                    $whereCond .= " and (upper(trans_app) = 'N' or trans_app is null)";
                }
            }
            if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(trans_status) = upper('" . $arr['search_by_status'] . "')";
            }
        }

        $sql = " 
			SELECT RECNUM, trans_id, messenger_id, trans_date, trans_app, trans_status, messenger_name
			FROM (SELECT trans_id, messenger_id, trans_date, trans_app, messenger_name,
			trans_status, ROWNUM RECNUM
			FROM (SELECT trans_id, messenger_name,
			TO_CHAR (trans_date, 'dd/mm/yyyy') trans_date,
			trans_app, trans_status, messenger_id
			FROM fm_mst_messenger mst INNER JOIN fm_trans_messenger trans
			ON mst.messenger_id = trans.trans_messenger_id
			WHERE TRUNC (trans.trans_date) >= TRUNC (SYSDATE) 
			--AND trans.trans_app = 'N' 
			$whereCond 
			ORDER BY trans_date ASC)) $limit
		";
								 
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function search_approval_messenger($arr, $limit = NULL) {
        $whereCond = "";
        if (!empty($arr)) {
            if (!empty($arr['search_by_messenger'])) {
                $whereCond .= " and messenger_id=" . $arr['search_by_messenger'];
            }
            if (!empty($arr['search_by_date'])) {
                $whereCond .= " and to_char(trans_date, 'dd-mm-yyyy') = '" . $arr['search_by_date'] . "'";
            }
            if (!empty($arr['search_by_approval'])) {
                if(strtoupper($arr['search_by_approval'])=='Y'){
                    $whereCond .= " and upper(trans_app) = 'Y'";
                }else{
                    $whereCond .= " and (upper(trans_app) = 'N' or trans_app is null)";
                }
            }
            if (!empty($arr['search_by_status'])) {
                $whereCond .= " and upper(trans_status) = upper('" . $arr['search_by_status'] . "')";
            }
        }

        $sql = " 
			SELECT RECNUM, trans_id, messenger_id, trans_date, trans_app, trans_status, messenger_name
			FROM (SELECT trans_id, messenger_id, trans_date, trans_app, messenger_name,
			trans_status, ROWNUM RECNUM
			FROM (SELECT trans_id, messenger_name,
			TO_CHAR (trans_date, 'dd/mm/yyyy') trans_date,
			trans_app, trans_status, messenger_id
			FROM fm_mst_messenger mst INNER JOIN fm_trans_messenger trans
			ON mst.messenger_id = trans.trans_messenger_id
			WHERE TRUNC (trans.trans_date) >= TRUNC (SYSDATE) 
			--AND trans.trans_app = 'N' 
			$whereCond 
			ORDER BY trans_date ASC)) $limit					 
		";
								 
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function update_approval_messenger($arr) {
        /*
		$trans_app = empty($arr['trans_app']) ? 'N' : 'Y';
        $trans_id = $arr['trans_id'];
        $login = $this->session->userdata('login');
        $user = $login['HR_ID'];

        $sql = "update fm_trans_messenger set 
					trans_app = '$trans_app', 
					trans_app_by = '$user', 
					trans_app_date = sysdate 
				where trans_id ='$trans_id' ";
        $this->db->query($sql);
		*/
    }
	
	function update_status_messanger($arr) {
        $messenger_trans_id = $arr['messenger_trans_id'];
        $cond = $arr['cond'];
		$login = $this->session->userdata('login');
        $user = $login['HR_ID'];
        
        if (!empty($messenger_trans_id)) {
            $sql_update = " 
				update fm_trans_messenger set trans_app = '$cond', 
					trans_app_by = '$user', 
					trans_app_date = sysdate  
                where trans_id = $messenger_trans_id 
			";
			
            $this->db->query($sql_update);
        }
    }

    //=======================================================================================================================
    // END OF APPROVAL MESSENGER MODEL
    //=======================================================================================================================

}